using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Diagnostics;
using System.Linq;
using System.Transactions;
using DataObject.Core;
using DataObject.Database;
using DataObject.Helpers;
using DataObject.Model;

namespace DataObject.DataAccess
{
    public class ReservationDAO : DataBaseProcessBase<Reservation, OBSDataContext>
    {
        #region Singleton

        public static ReservationDAO CreateInstant()
        {
            return new ReservationDAO();
        }

        #endregion

        #region IsTextIdExisted Method

        public bool IsTextIdExisted(string textId)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<Reservation> query =
                    (from table in db.Reservations where table.ReservationCode.Equals(textId) select table);
                if (query.Any())
                    return true;
                return false;
            }
        }

        #endregion

        #region GetUniqueTextIdFromUnicodeText Method

        public string GetUniqueTextIdFromUnicodeText(string textId, int maxLength)
        {
            var unicodeConversion = new UnicodeConversion();
            string textIdValue = unicodeConversion.GetStringId(unicodeConversion.UnicodeToASCII2(textId), maxLength);
            string toReturn = textIdValue;
            int index = 2;
            while (IsTextIdExisted(toReturn))
            {
                toReturn = textIdValue + "-" + index;
                if (toReturn.Length > maxLength)
                    toReturn = textIdValue.Substring(0, maxLength - ("-" + index).Length) + "-" + index;
                index++;
            }
            return toReturn;
        }

        #endregion

        #region GetReservationByReservationCode Method

        public Reservation GetByReservationCode(string reservationCode, Guid? reservationId = null)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                string bookingCode = reservationCode.Trim().ToLower();
                if (reservationId.HasValue && reservationId.Value != Guid.Empty)
                {
                    return
                        db.Reservations.SingleOrDefault(
                            a =>
                                a.ReservationCode.ToLower().Equals(bookingCode) &&
                                a.ReservationID != reservationId.Value);
                }
                return db.Reservations.SingleOrDefault(a => a.ReservationCode.ToLower().Equals(bookingCode));
            }
        }

        #endregion

        #region GetViewReservation Method

        public List<ViewReservation> GetViewReservation(string boatID, DateTime? checkIn, string email, string fax,
            string tel, string reservationCode, string bookedPerson, HashSet<string> sortClause)
        {
            int count;
            return GetViewReservation(boatID, checkIn, email, fax, tel, reservationCode, bookedPerson, null, null,
                out count, sortClause);
        }

        public List<ViewReservation> GetViewReservation(string boatID, DateTime? checkIn, string email, string fax,
            string tel, string reservationCode, string bookedPerson, int? page, int? row, out int count,
            HashSet<string> sortClause)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<ViewReservation> query = (from table in db.ViewReservations select table);
                if (!boatID.Equals(string.Empty))
                    query = query.Where(table => table.BoatID.ToLower().Equals(boatID.ToLower()));
                if (checkIn.HasValue)
                    query = query.Where(table => table.CheckInDate.Date == checkIn.Value.Date);
                if (!email.Equals(string.Empty))
                    query = query.Where(table => table.Email.Equals(email.ToLower()));
                if (!bookedPerson.Equals(string.Empty))
                    query =
                        query.Where(
                            table => (table.FirstName.Contains(bookedPerson) || table.Lastname.Equals(bookedPerson)));
                if (!reservationCode.Equals(string.Empty))
                    query = query.Where(table => table.ReservationCode.Contains(reservationCode));
                for (int i = sortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(sortClause.ElementAt(i));
                count = query.Count();
                query = query.OrderByDescending(b => b.CreationDate);
                if (page.HasValue && row.HasValue)
                    return query.PageIQuery(page.Value, row.Value).ToList();
                return query.ToList();
            }
        }

        public List<ViewReservation> GetViewReservation(
            DateTime? fromDepDate, DateTime? toDepDate,
            DateTime? fromCreatedDate, DateTime? toCreatedDate,
            string reservationCode, string boatID, int status, int clientType, int routeNumber, char? subRouteCode,
            int? page, int? row, out int count, HashSet<string> sortClause)
        {
            try
            {
                using (var db = new OBSDataContext(connectionString))
                {
                    IQueryable<ViewReservation> query = (from table in db.ViewReservations select table);
                    if (!boatID.Equals(string.Empty))
                        query = query.Where(table => table.BoatID.ToLower().Equals(boatID.ToLower()));
                    if (fromDepDate.HasValue)
                        query = query.Where(table => table.CheckInDate.Date >= fromDepDate.Value.Date);
                    if (toDepDate.HasValue)
                        query = query.Where(table => table.CheckInDate.Date < toDepDate.Value.Date.AddDays(1));
                    if (fromCreatedDate.HasValue)
                        query = query.Where(table => table.CreationDate.Date >= fromCreatedDate.Value.Date);
                    if (toCreatedDate.HasValue)
                        query = query.Where(table => table.CreationDate.Date < toCreatedDate.Value.Date.AddDays(1));
                    if (!clientType.Equals(-1))
                        query = query.Where(table => table.GuestType.Equals(clientType));
                    if (!reservationCode.Equals(string.Empty))
                        query = query.Where(table => table.ReservationCode.Contains(reservationCode));
                    if (!status.Equals(-1))
                        query = query.Where(table => table.Status.Equals(status));
                    if (!routeNumber.Equals(-1))
                        query = query.Where(table => table.RouteNumber.Equals(routeNumber));
                    if (subRouteCode.HasValue && subRouteCode.Value != ' ')
                        query = query.Where(x => x.SubRouteCode == subRouteCode.Value);
                    for (int i = sortClause.Count - 1; i >= 0; i--)
                        query = query.OrderBy(sortClause.ElementAt(i));
                    count = query.Count();
                    query = query.OrderByDescending(b => b.CreationDate);
                    if (page.HasValue && row.HasValue)
                        return query.PageIQuery(page.Value, row.Value).ToList();
                    return query.ToList();
                }
            }
            catch (Exception)
            {
                count = 0;
                return null;
            }
        }

        #endregion

        #region Reports

        #region GetViewAccountantReport Method

        public List<ViewAccountantReport> GetViewAccountantReport(DateTime? fromDate, DateTime? toDate, int clientType,
            string boatID, string agentID, int? page, int? row, out int count, HashSet<string> sortClause)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<ViewAccountantReport> query = (from table in db.ViewAccountantReports select table);
                if (!agentID.Equals(string.Empty))
                    query = query.Where(table => table.GuestID.ToLower().Equals(agentID.ToLower()));
                if (!boatID.Equals(string.Empty))
                    query = query.Where(table => table.BoatID.ToLower().Equals(boatID.ToLower()));
                if (!clientType.Equals(-1))
                    query = query.Where(table => table.GuestType.Equals(clientType));
                if (fromDate.HasValue)
                    query = query.Where(table => table.CheckInDate.Date >= fromDate.Value.Date);
                if (toDate.HasValue)
                    query = query.Where(table => table.CheckInDate.Date < toDate.Value.Date.AddDays(1));
                for (int i = sortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(sortClause.ElementAt(i));
                count = query.Count();
                query = query.OrderBy(b => b.CheckInDate).ThenBy(b => b.ReservationCode);
                if (page.HasValue && row.HasValue)
                    query = query.PageIQuery(page.Value, row.Value);
                return query.ToList();
            }
        }

        #endregion

        #region GetViewOperatorReport Method

        public IList<ViewOperatorReport> GetViewOperatorReport(DateTime fromDate, DateTime toDate, string boatID,
            int? page, int? row, out int count, HashSet<string> sortClause)
        {
            IList<ViewOperatorReport> collection;
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<ViewOperatorReport> query = db.ViewOperatorReports.Between(x => x.Date.Date, fromDate.Date,
                    toDate.Date);

                if (!boatID.Equals(string.Empty))
                    query = query.Where(table => table.BoatID.ToLower().Equals(boatID.ToLower()));
                for (int i = sortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(sortClause.ElementAt(i));
                count = query.Count();
                query = query.OrderBy(o => o.Date).ThenBy(o => o.BoatID).ThenBy(o => o.ReservationCode);
                if (page.HasValue && row.HasValue)
                {
                    collection = query.PageIQuery(page.Value, row.Value).ToList();
                }
                else
                {
                    collection = query.ToList();
                }

                foreach (ViewOperatorReport item in collection)
                {
                    IQueryable<PaymentBatch> payments = from p in db.PaymentBatches
                        where p.ReservationPayments.Count(x => x.ReservationID == item.ReservationID) > 0
                        select p;
                    if (payments.Any())
                    {
                        item.FillPayment(payments);
                    }
                }
            }

            return collection;
        }

        #endregion

        #region GetViewAccountPayableHeader Method

        public List<ViewAccountPayableHeader> GetViewAccountPayableHeader(DateTime? fromDate, DateTime? toDate,
            string itemCode, string serviceName, string providerName, int? page, int? row, out int count,
            HashSet<string> sortClause)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<ViewAccountPayableHeader> query = (from table in db.ViewAccountPayableHeaders select table);
                if (!itemCode.Equals(string.Empty))
                    query = query.Where(table => table.ItemCode.ToLower().Equals(itemCode.ToLower()));
                if (!providerName.Equals(string.Empty))
                    query = query.Where(table => table.ProviderName.ToLower().Contains(providerName.ToLower()));
                if (!serviceName.Equals(string.Empty))
                    query = query.Where(table => table.ServiceName.ToLower().Equals(serviceName.ToLower()));
                if (fromDate.HasValue)
                    query = query.Where(table => table.Date >= fromDate.Value.Date);
                if (toDate.HasValue)
                    query = query.Where(table => table.Date < toDate.Value.Date.AddDays(1));
                for (int i = sortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(sortClause.ElementAt(i));
                count = query.Count();
                query = query.OrderBy(b => b.Date).ThenBy(b => b.ProviderName).ThenBy(b => b.ServiceName);
                if (page.HasValue && row.HasValue)
                    query = query.PageIQuery(page.Value, row.Value);
                return query.ToList();
            }
        }

        #endregion

        #region GetViewAccountPayableDetail Method

        public List<ViewAccountPayableDetail> GetViewAccountPayableDetails(DateTime? fromDate, DateTime? toDate,
            int clientType, string boatID, string providerName, int? page, int? row, out int count,
            HashSet<string> sortClause)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<ViewAccountPayableDetail> query = (from table in db.ViewAccountPayableDetails select table);
                if (!providerName.Equals(string.Empty))
                    query = query.Where(table => table.ProviderName.ToLower().Equals(providerName.ToLower()));
                if (!boatID.Equals(string.Empty))
                    query = query.Where(table => table.BoatID.ToLower().Equals(boatID.ToLower()));
                if (!clientType.Equals(-1))
                    query = query.Where(table => table.GuestType.Equals(clientType));
                if (fromDate.HasValue)
                    query = query.Where(table => table.Date >= fromDate.Value.Date);
                if (toDate.HasValue)
                    query = query.Where(table => table.Date < toDate.Value.Date.AddDays(1));
                for (int i = sortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(sortClause.ElementAt(i));
                count = query.Count();
                if (page.HasValue && row.HasValue)
                    query = query.PageIQuery(page.Value, row.Value);
                return query.ToList();
            }
        }

        #endregion

        #region GetViewServiceReport Method

        public List<ViewServicesReport> GetViewServicesReport(
            DateTime? fromDate, DateTime? toDate,
            DateTime? fromServiceDate, DateTime? toServiceDate,
            int clientType, int serviceType,
            string boatID, string serviceId,
            Guid? providerId, int? page,
            int? row, out int count, HashSet<string> sortClause)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<ViewServicesReport> query = (from table in db.ViewServicesReports select table);

                // -- Enum --
                if (!clientType.Equals(define.ALL))
                    query = query.Where(x => x.GuestType.Equals(clientType));
                if (!serviceType.Equals(define.ALL))
                    query = query.Where(x => x.ServiceType.Equals(serviceType));
                // -- Unique --
                if (providerId != null)
                    query = query.Where(x => x.ProviderID.Equals(providerId));
                // -- String --
                if (!String.IsNullOrEmpty(boatID))
                    query = query.Where(x => x.BoatID.ToLower().Equals(boatID.ToLower()));
                if (!String.IsNullOrEmpty(serviceId))
                {
                    int serviceNum;
                    if (int.TryParse(serviceId, out serviceNum))
                    {
                        // zero is meals type
                        query = query.Where(x => x.ServiceType.Equals(serviceNum));
                    }
                    else
                    {
                        var serviceGuid = new Guid(serviceId);
                        query = query.Where(x => x.ServiceID.Equals(serviceGuid));
                    }
                }
                // -- Date --
                if (fromDate.HasValue)
                    query = query.Where(x => x.DepartureDate >= fromDate.Value.Date);
                if (toDate.HasValue)
                    query = query.Where(x => x.DepartureDate < toDate.Value.Date.AddDays(1));
                if (fromServiceDate.HasValue)
                    query = query.Where(x => x.ServiceDate >= fromServiceDate.Value.Date);
                if (toServiceDate.HasValue)
                    query = query.Where(x => x.ServiceDate < toServiceDate.Value.Date.AddDays(1));
                // -- Sort --
                for (int i = sortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(sortClause.ElementAt(i));
                count = query.Count();
                if (page.HasValue && row.HasValue)
                    query = query.PageIQuery(page.Value, row.Value);
                return query.ToList();
            }
        }

        #endregion

        private const string URLVendor =
            @"/Pages/Reports/ServiceReport.aspx?ServiceID={0}&BoatID={1}&ClientType={2}&ServiceType={3}&FromDate={4:d}&ToDate={5:d}&FromServiceDate={6:d}&ToServiceDate={7:d}&ProviderID={8}&XLS=1";

        private const string URLGuest =
            "/Pages/Reports/AccReceivableReport.aspx?AgentID={0}&BoatID={1}&ClientType={2}&FromDate={3:d}&ToDate={4:d}&GuestID={5}&XLS=1";

        public IList<object> GetProvider(DateTime? fromDate, DateTime? toDate,
            DateTime? fromServiceDate, DateTime? toServiceDate,
            int clientType, int serviceType, string boatId, string serviceId)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<ViewServicesReport> query = (from table in db.ViewServicesReports select table);
                if (!String.IsNullOrEmpty(serviceId))
                {
                    int serviceNum;
                    if (int.TryParse(serviceId, out serviceNum))
                    {
                        // zero is meals type
                        query = query.Where(x => x.ServiceType.Equals(serviceNum));
                    }
                    else
                    {
                        var serviceGuid = new Guid(serviceId);
                        query = query.Where(x => x.ServiceID.Equals(serviceGuid));
                    }
                }
                if (!boatId.Equals(string.Empty))
                    query = query.Where(x => x.BoatID.ToLower().Equals(boatId.ToLower()));
                if (!clientType.Equals(-1))
                    query = query.Where(x => x.GuestType.Equals(clientType));
                if (!serviceType.Equals(-1))
                    query = query.Where(x => x.ServiceType.Equals(serviceType));
                // -- Date --
                if (fromDate.HasValue)
                    query = query.Where(x => x.DepartureDate >= fromDate.Value.Date);
                if (toDate.HasValue)
                    query = query.Where(x => x.DepartureDate < toDate.Value.Date.AddDays(1));
                if (fromServiceDate.HasValue)
                    query = query.Where(x => x.ServiceDate >= fromServiceDate.Value.Date);
                if (toServiceDate.HasValue)
                    query = query.Where(x => x.ServiceDate < toServiceDate.Value.Date.AddDays(1));
                return
                    query.Where(x => x.ProviderID != null)
                        .GroupBy(x => new {x.ProviderID, x.ProviderName})
                        .Select(x => (object) new
                        {
                            x.Key.ProviderID,
                            x.Key.ProviderName,
                            Bookings = x.GroupBy(z => z.ReservationCode).Count(),
                            ItemCount = x.Count(),
                            Url =
                                String.Format(URLVendor, serviceId, boatId, clientType, serviceType, fromDate, toDate,
                                    fromServiceDate, toServiceDate, x.Key.ProviderID)
                        }).ToList();
            }
        }

        public IList<object> GetCustomer(DateTime? fromDate, DateTime? toDate, int clientType, string boatID,
            string agentID)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<ViewAccountReceivableHeader> query =
                    (from table in db.ViewAccountReceivableHeaders select table);
                if (!agentID.Equals(string.Empty))
                    query = query.Where(x => x.GuestID.ToLower().Equals(agentID.ToLower()));
                if (!boatID.Equals(string.Empty))
                    query = query.Where(table => table.BoatID.ToLower().Equals(boatID.ToLower()));
                if (!clientType.Equals(-1))
                    query = query.Where(table => table.GuestType.Equals(clientType));
                if (fromDate.HasValue)
                    query = query.Where(table => table.DepartureDate >= fromDate.Value.Date);
                if (toDate.HasValue)
                    query = query.Where(table => table.DepartureDate < toDate.Value.Date.AddDays(1));
                return query.GroupBy(x => x.GuestID).Select(x => (object) new
                {
                    GuestID = x.Key,
                    Bookings = x.Count(),
                    Url = String.Format(URLGuest, agentID, boatID, clientType, fromDate, toDate, x.Key)
                }).ToList();
            }
        }

        #region GetViewPaymentReport Method

        public List<ViewPaymentReport> GetViewPaymentReport(
            DateTime? fromDate,
            DateTime? toDate,
            int clientType,
            int paidMethod,
            string boatID,
            string agentID,
            int? page,
            int? row,
            out int count,
            HashSet<string> sortClause)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<ViewPaymentReport> query = (from table in db.ViewPaymentReports select table);
                if (!agentID.Equals(string.Empty))
                    query = query.Where(table => table.GuestID.ToLower().Contains(agentID.ToLower()));
                if (!boatID.Equals(string.Empty))
                    query = query.Where(table => table.BoatID.ToLower().Equals(boatID.ToLower()));
                if (!paidMethod.Equals(0))
                {
                    query = paidMethod.Equals(1) ? query.Where(table => table.ReceivedBy.ToLower().Contains("linh")) : query.Where(table => table.PaymentMethod.Equals(paidMethod - 2));
                }
                if (!clientType.Equals(-1))
                    query = query.Where(table => table.GuestType.Equals(clientType));
                if (fromDate.HasValue)
                    query = query.Where(table => table.DepartureDate >= fromDate.Value.Date);
                if (toDate.HasValue)
                    query = query.Where(table => table.DepartureDate < toDate.Value.Date.AddDays(1));
                for (int i = sortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(sortClause.ElementAt(i));
                count = query.Count();
                if (page.HasValue && row.HasValue)
                    query = query.PageIQuery(page.Value, row.Value);
                return query.ToList();
            }
        }

        #endregion

        #region GetViewSummaryReport Method

        public List<ViewSummaryReport> GetViewSummaryReport(
            DateTime? fromDate,
            DateTime? toDate,
            int clientType,
            int paidMethod,
            string boatID,
            string agentID,
            int? page,
            int? row,
            out int count,
            HashSet<string> sortClause)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<ViewSummaryReport> query = (from table in db.ViewSummaryReports select table);
                if (!agentID.Equals(string.Empty))
                    query = query.Where(table => table.GuestID.ToLower().Contains(agentID.ToLower()));
                if (!boatID.Equals(string.Empty))
                    query = query.Where(table => table.BoatID.ToLower().Equals(boatID.ToLower()));
                if (!paidMethod.Equals(0))
                {
                    query = paidMethod.Equals(1) ? query.Where(table => table.ReceivedBy.ToLower().Contains("linh")) : query.Where(table => table.PaymentMethod.Equals(paidMethod - 2));
                }
                if (!clientType.Equals(-1))
                    query = query.Where(table => table.GuestType.Equals(clientType));
                if (fromDate.HasValue)
                    query = query.Where(table => table.DepartureDate >= fromDate.Value.Date);
                if (toDate.HasValue)
                    query = query.Where(table => table.DepartureDate < toDate.Value.Date.AddDays(1));
                for (int i = sortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(sortClause.ElementAt(i));
                count = query.Count();
                if (page.HasValue && row.HasValue)
                    query = query.PageIQuery(page.Value, row.Value);
                return query.ToList();
            }
        }

        #endregion

        #region GetViewIncomeReport Method

        public List<ViewPaymentReport> GetViewIncomeReport(
            DateTime? fromDate,
            DateTime? toDate,
            int clientType,
            int paidMethod,
            string boatID,
            string agentID,
            int? page,
            int? row,
            out int count,
            HashSet<string> sortClause)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<ViewPaymentReport> query = (from table in db.ViewPaymentReports
                    where table.DepartureDate > table.PaymentDate
                    select table);
                if (!agentID.Equals(string.Empty))
                    query = query.Where(table => table.GuestID.ToLower().Contains(agentID.ToLower()));
                if (!boatID.Equals(string.Empty))
                    query = query.Where(table => table.BoatID.ToLower().Equals(boatID.ToLower()));
                if (!paidMethod.Equals(0))
                {
                    query = paidMethod.Equals(1) ? query.Where(table => table.ReceivedBy.ToLower().Contains("linh")) : query.Where(table => table.PaymentMethod.Equals(paidMethod - 2));
                }
                if (!clientType.Equals(-1))
                    query = query.Where(table => table.GuestType.Equals(clientType));
                if (fromDate.HasValue)
                    query = query.Where(table => table.PaymentDate >= fromDate.Value.Date);
                if (toDate.HasValue)
                    query = query.Where(table => table.PaymentDate < toDate.Value.Date.AddDays(1));
                for (int i = sortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(sortClause.ElementAt(i));
                count = query.Count();
                if (page.HasValue && row.HasValue)
                    query = query.PageIQuery(page.Value, row.Value);
                return query.ToList();
            }
        }

        #endregion

        #region GetViewAccountReceivableHeader Method

        public List<ViewAccountReceivableHeader> GetViewAccountReceivableHeader(DateTime? fromDate, DateTime? toDate,
            int clientType, string boatID, string agentID, int? page, int? row, out int count,
            HashSet<string> sortClause)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<ViewAccountReceivableHeader> query =
                    (from table in db.ViewAccountReceivableHeaders select table);
                if (!agentID.Equals(string.Empty))
                    query = query.Where(table => table.GuestID.ToLower().Equals(agentID.ToLower()));
                if (!boatID.Equals(string.Empty))
                    query = query.Where(table => table.BoatID.ToLower().Equals(boatID.ToLower()));
                if (!clientType.Equals(-1))
                    query = query.Where(table => table.GuestType.Equals(clientType));
                if (fromDate.HasValue)
                    query = query.Where(table => table.DepartureDate >= fromDate.Value.Date);
                if (toDate.HasValue)
                    query = query.Where(table => table.DepartureDate < toDate.Value.Date.AddDays(1));
                for (int i = sortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(sortClause.ElementAt(i));
                count = query.Count();
                query = query.OrderBy(b => b.DepartureDate).ThenBy(b => b.ReservationCode);
                if (page.HasValue && row.HasValue)
                    query = query.PageIQuery(page.Value, row.Value);
                return query.ToList();
            }
        }

        #endregion

        #endregion

        #region GetViewAlarm Method

        public List<Reservation> GetReservationByAlarm(DateTime alarm)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<Reservation> query = (from table in db.Reservations
                    where (table.Alarm != null
                           && table.Alarm <= alarm.Date)
                          && (table.CheckInDate >= DateTime.Today)
                          && (table.CheckInDate >= alarm.Date)
                          && (table.Status != 1)
                    // Cancelled
                    select table).OrderBy(table => table.CheckInDate).Take(15);
                return query.ToList();
            }
        }

        #endregion

        #region Main Transaction

        #region Create Reservation (Use transaction)

        public bool CreateReservation(Reservation reservation, Guest guest, IList<PaymentBatch> paymentBatches)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                // Insert guest WI type
                if (guest.GuestType == 1)
                {
                    // Insert WI
                    double i = 0;

                    // Get guest list (not agents).
                    IQueryable<Guest> guests = db.Guests.Where(g => g.GuestID.StartsWith("WI/") && g.GuestType == 1);
                    if (guests.Any())
                    {
                        string maxID = guests.Max(x => x.GuestID);
                        if (maxID.Length > 3 && double.TryParse(maxID.Substring(3), out i)) i++;
                    }
                    guest.GuestID = String.Format("WI/{0:00000000}", i);
                    guest.UserId = Guid.Empty;
                    db.Guests.InsertOnSubmit(guest);
                }

                // Insert payment batch and details
                if (paymentBatches != null && paymentBatches.Count > 0)
                {
                    foreach (PaymentBatch item in paymentBatches)
                    {
                        item.PaymentBatchID = Helper.NewSeqGuid();
                        item.PaidAmount = item.PaymentBatchDetails.Sum(x => x.Amount*x.Rate);
                        item.ModifiedBy = reservation.ModifiedByID;
                        item.ModifiedDate = reservation.ModifiedDate;
                        foreach (PaymentBatchDetail detail in item.PaymentBatchDetails)
                        {
                            detail.PaymentBatchDetailID = Helper.NewSeqGuid();
                            detail.Active = true;
                        }

                        reservation.ReservationPayments.Add(new ReservationPayment
                        {
                            ReservationPaymentID = Helper.NewSeqGuid(),
                            ReservationID = reservation.ReservationID,
                            PaymentBatch = item,
                            PaymentBatchID = item.PaymentBatchID,
                            IsDeposit = true
                        });
                    }
                }

                // Update guest ID
                reservation.GuestID = guest.GuestID;
                reservation.NumOfCabins = reservation.ReservationDetails.Count;

                db.Reservations.InsertOnSubmit(reservation);

                db.SubmitChanges();
            }
            return true;
        }

        #endregion

        #region Update Reservation (Use transaction)

        public bool UpdateReservation(Reservation reservation, Guest guest, IList<PaymentBatch> payments)
        {
            bool result = false;
            using (var db = new OBSDataContext(connectionString))
            {
                using (var scope = new TransactionScope())
                {
                    string guestID = guest.GuestID;
                    Guest queryGuest = db.Guests.SingleOrDefault(x => x.GuestID == guestID);
                    if (queryGuest != null)
                    {
                        queryGuest.AddressLine1 = guest.AddressLine1;
                        queryGuest.AddressLine2 = guest.AddressLine2;
                        queryGuest.City = guest.City;
                        queryGuest.CompanyName = guest.CompanyName;
                        queryGuest.CountryID = guest.CountryID;
                        queryGuest.Email = guest.Email;
                        queryGuest.Fax = guest.Fax;
                        queryGuest.FirstName = guest.FirstName;
                        queryGuest.Lastname = guest.Lastname;
                        queryGuest.Tel = guest.Tel;
                        queryGuest.Title = guest.Title;
                        queryGuest.ZipCode = guest.ZipCode;
                    }

                    db.SubmitChanges();

                    Reservation query =
                        db.Reservations.SingleOrDefault(r => r.ReservationID == reservation.ReservationID);
                    if (query != null)
                    {
                        Guid reservationID = query.ReservationID;

                        // Delete children
                        IQueryable<CabinStatusEntity> cabinStatuses =
                            db.CabinStatusEntities.Where(x => x.ReservationDetail.ReservationID == reservationID);
                        IQueryable<ReservationDetail> reservationDetails =
                            db.ReservationDetails.Where(x => x.ReservationID == reservationID);
                        IQueryable<ReservationPayment> reservationPayments =
                            db.ReservationPayments.Where(x => x.ReservationID == reservationID);
                        IQueryable<PaymentBatch> paymentBatches =
                            db.PaymentBatches.Where(
                                x => x.ReservationPayments.Count(y => y.ReservationID == reservationID) > 0);
                        IQueryable<PaymentBatchDetail> paymentBatchDetails =
                            db.PaymentBatchDetails.Where(
                                x =>
                                    x.PaymentBatch.ReservationPayments.Count(y => y.ReservationID == reservationID) >
                                    0);

                        db.CabinStatusEntities.DeleteAllOnSubmit(cabinStatuses);
                        db.ReservationDetails.DeleteAllOnSubmit(reservationDetails);

                        db.PaymentBatchDetails.DeleteAllOnSubmit(paymentBatchDetails);
                        db.PaymentBatches.DeleteAllOnSubmit(paymentBatches);

                        db.ReservationPayments.DeleteAllOnSubmit(reservationPayments);

                        db.SubmitChanges();

                        // Submit details
                        foreach (ReservationDetail item in reservation.ReservationDetails.ToList())
                        {
                            query.ReservationDetails.Add(item);
                        }

                        db.SubmitChanges();

                        if (payments != null && payments.Count > 0)
                        {
                            // Submit payments
                            foreach (PaymentBatch item in payments.ToList())
                            {
                                item.PaymentBatchID = Helper.NewSeqGuid();
                                item.ModifiedBy = query.ModifiedByID;
                                item.ModifiedDate = query.ModifiedDate;
                                item.PaidAmount = item.PaymentBatchDetails.Sum(x => x.Amount*x.Rate);

                                foreach (PaymentBatchDetail detail in item.PaymentBatchDetails.ToList())
                                {
                                    detail.PaymentBatchDetailID = Helper.NewSeqGuid();
                                    detail.Active = true;
                                }

                                db.PaymentBatches.InsertOnSubmit(item);

                                query.ReservationPayments.Add(new ReservationPayment
                                {
                                    ReservationPaymentID = Helper.NewSeqGuid(),
                                    ReservationID = reservationID,
                                    PaymentBatchID = item.PaymentBatchID,
                                    IsDeposit = item.DepositFlag
                                });
                            }
                        }

                        // Update
                        UpdateMapper(reservation, query);

                        db.SubmitChanges();

                        scope.Complete();
                        result = true;
                    }
                }
            }
            return result;
        }

        /// <summary>
        ///     Update boat for the reservation
        /// </summary>
        /// <param name="boatId"></param>
        /// <param name="departureDate"></param>
        /// <param name="boardingDate"></param>
        /// <param name="numOfNights"></param>
        /// <param name="numOfCabins"></param>
        /// <param name="reservationId"></param>
        /// <exception cref="ArgumentNullException"></exception>
        /// <returns></returns>
        public bool UpdateBoatForReservation(Guid reservationId, string boatId, DateTime departureDate, DateTime boardingDate, int numOfNights, int numOfCabins)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                using (var scope = new TransactionScope())
                {
                    Reservation booking = db.Reservations.SingleOrDefault(x => x.ReservationID == reservationId);

                    if (booking == null)
                    {
                        throw new ArgumentNullException(@"reservationId");
                    }
                    if (!Equals(booking.DepartureDate, departureDate))
                    {
                        // Change departure date (no effect to the cabins)
                        booking.DepartureDate = departureDate;
                    }

                    bool boardingDateChanged = (booking.CheckInDate != boardingDate);
                    if (boardingDateChanged)
                    {
                        // Change boarding date (effect to the cabins)
                        booking.CheckInDate = boardingDate;
                    }

                    IQueryable<ViewCabinBoat> freeCabins = GetFreeCabins(reservationId, boatId, boardingDate,
                        numOfNights, db);

                    int oldNumOfCabins = booking.NumOfCabins;
                    if (freeCabins == null || !freeCabins.Any() ||
                        freeCabins.Count() < numOfCabins - oldNumOfCabins)
                        throw new InvalidOperationException("Not enough the cabins");

                    bool boatingChanged = (String.Compare(booking.BoatID, boatId, StringComparison.OrdinalIgnoreCase) != 0);
                    int? latestCabinStatus = null;

                    //if (isCharter)
                    //    numOfCabins = db.CabinEntities.Where(n => n.BoatID.Equals(boatID)).Count();

                    // Change boarding date but the cabins booked are not conflicted with the another booking.
                    bool conflictChangedDate = (boardingDateChanged &&
                                                !booking.ReservationDetails.Any(
                                                    x => freeCabins.Any(c => c.CabinID == x.CabinID)));

                    if (boatingChanged || conflictChangedDate || booking.NumOfNights != numOfNights)
                    {
                        // Changed
                        booking.BoatID = boatId;
                        booking.NumOfNights = numOfNights;

                        // Get the matched cabins
                        List<ViewCabinBoat> freeCabinList = freeCabins.Take(numOfCabins).ToList();

                        // Process the details and the cabin status
                        int i = 0;
                        foreach (ReservationDetail detail in booking.ReservationDetails.Take(numOfCabins))
                        {
                            // Change from the cabin of old boat to the cabin of new boat
                            int cabinID = freeCabinList[i++].CabinID;
                            if (detail != null && detail.CabinID != cabinID) detail.CabinID = cabinID;

                            // Remove cabin status of old boat
                            if (detail != null)
                            {
                                db.CabinStatusEntities.DeleteAllOnSubmit(detail.CabinStatusEntities);

                                // Add new cabin status of new boat
                                for (DateTime d = boardingDate; d < boardingDate.AddDays(numOfNights); d = d.AddDays(1))
                                {
                                    db.CabinStatusEntities.InsertOnSubmit(new CabinStatusEntity
                                    {
                                        CabinID = cabinID,
                                        Date = d,
                                        RDID = detail.RDID,
                                        Status = detail.CabinStatus
                                    });
                                }

                                // this line getted to update for new detail below
                                latestCabinStatus = detail.CabinStatus;
                            }
                        }

                        db.SubmitChanges();

                        // Unusage cabins of old boat
                        IEnumerable<ReservationDetail> unusageCabin = booking.ReservationDetails.Skip(numOfCabins);
                        var reservationDetails = unusageCabin as IList<ReservationDetail> ?? unusageCabin.ToList();
                        if (reservationDetails.Any())
                        {
                            // Remove them (Cascade delete with Cabin status)
                            db.ReservationDetails.DeleteAllOnSubmit(reservationDetails);
                        }
                        else if (booking.NumOfCabins < numOfCabins)
                        {
                            latestCabinStatus = latestCabinStatus ?? 1;
                            // Insert the cabins of new boat over the number of cabins of the old boat
                            for (; i < numOfCabins;)
                            {
                                int cabinId = freeCabinList[i++].CabinID;
                                Guid rdid = Helper.NewSeqGuid();
                                var detail = new ReservationDetail
                                {
                                    RDID = rdid,
                                    ReservationID = booking.ReservationID,
                                    CabinID = cabinId,
                                    Representative = booking.ReservationCode,
                                    Status = 1,
                                    CabinStatus = latestCabinStatus.Value,
                                    CabinStatusEntities = new EntitySet<CabinStatusEntity>()
                                };

                                for (DateTime d = boardingDate;
                                    d < boardingDate.AddDays(numOfNights);
                                    d = d.AddDays(1))
                                {
                                    detail.CabinStatusEntities.Add(new CabinStatusEntity
                                    {
                                        CabinID = cabinId,
                                        Date = d,
                                        RDID = rdid,
                                        Status = detail.CabinStatus
                                    });
                                }

                                booking.ReservationDetails.Add(detail);
                            }
                        }

                        // this case is correctly
                    }
                    else
                    {
                        // Boating not change or boarding date have not cabins conflicted

                        // In case, change cabins
                        if (numOfCabins > oldNumOfCabins)
                        {
                            freeCabins = GetFreeCabins(Guid.Empty, boatId, boardingDate, numOfNights, db);
                            List<ViewCabinBoat> freeCabinList =
                                freeCabins.Take(numOfCabins - oldNumOfCabins).ToList();

                            latestCabinStatus = booking.ReservationDetails.Last().CabinStatus;

                            foreach (ViewCabinBoat cabinBoat in freeCabinList)
                            {
                                int cabinID = cabinBoat.CabinID;
                                Guid rdid = Helper.NewSeqGuid();
                                var detail = new ReservationDetail
                                {
                                    RDID = rdid,
                                    ReservationID = booking.ReservationID,
                                    CabinID = cabinID,
                                    Representative = booking.ReservationCode,
                                    Status = 1,
                                    CabinStatus = latestCabinStatus.Value,
                                    CabinStatusEntities = new EntitySet<CabinStatusEntity>()
                                };

                                for (DateTime d = boardingDate;
                                    d < boardingDate.AddDays(numOfNights);
                                    d = d.AddDays(1))
                                {
                                    detail.CabinStatusEntities.Add(new CabinStatusEntity
                                    {
                                        CabinID = cabinID,
                                        Date = d,
                                        RDID = rdid,
                                        Status = detail.CabinStatus
                                    });
                                }

                                booking.ReservationDetails.Add(detail);
                            }
                        }
                        else if (numOfCabins < oldNumOfCabins)
                        {
                            IEnumerable<ReservationDetail> removedCabins =
                                booking.ReservationDetails.Skip(numOfCabins);
                            var reservationDetails = removedCabins as IList<ReservationDetail> ?? removedCabins.ToList();
                            if (reservationDetails.Any())
                            {
                                db.ReservationDetails.DeleteAllOnSubmit(reservationDetails);
                            }
                        }
                        else if (boardingDateChanged) // but not conflict
                        {
                            IQueryable<CabinStatusEntity> query =
                                db.CabinStatusEntities.Where(x => x.ReservationDetail.ReservationID == reservationId);
                            db.CabinStatusEntities.DeleteAllOnSubmit(query);

                            foreach (ReservationDetail item in booking.ReservationDetails)
                            {
                                for (DateTime d = boardingDate;
                                    d < boardingDate.AddDays(numOfNights);
                                    d = d.AddDays(1))
                                {
                                    db.CabinStatusEntities.InsertOnSubmit(new CabinStatusEntity
                                    {
                                        CabinID = item.CabinID,
                                        Date = d,
                                        RDID = item.RDID,
                                        Status = item.CabinStatus
                                    });
                                }
                            }
                        }
                    }

                    booking.NumOfCabins = numOfCabins;

                    db.SubmitChanges();

                    scope.Complete();
                }
            }
            return true;
        }

        private static IQueryable<ViewCabinBoat> GetFreeCabins(Guid reservationId, string boatId, DateTime boardingDate,
            int numOfNights, OBSDataContext db)
        {
            // Unfree cabins
            var unfreeCabins = from x in db.CabinStatusEntities
                join y in db.CabinEntities on x.CabinID equals y.CabinID
                where x.Date.Date >= boardingDate.Date
                      && x.Date.Date < boardingDate.Date.AddDays(numOfNights)
                      && y.BoatID.Equals(boatId)
                      && x.ReservationDetail.ReservationID != reservationId
                // exclude the current reservation with the reservation ID
                group y.CabinID by y.CabinID
                into temp
                where temp.Any()
                select new {CabinID = temp.Key, Count = temp.Count()};

            // Free cabins (Except unfree cabins)
            IQueryable<ViewCabinBoat> freeCabins =
                db.ViewCabinBoats.Where(x => String.Compare(x.BoatID, boatId, StringComparison.OrdinalIgnoreCase) == 0
                                             && !unfreeCabins.Select(y => y.CabinID).Contains(x.CabinID));
            return freeCabins;
        }

        private void UpdateMapper(Reservation source, Reservation target)
        {
            target.ReservationCode = source.ReservationCode;

            target.Alarm = source.Alarm;
            target.Note = source.Note;
            target.Pickup = source.Pickup;
            target.HotelAddress = source.HotelAddress;
            target.ConfirmationEmail = source.ConfirmationEmail;

            target.RouteNumber = source.RouteNumber;
            target.SubRouteCode = source.SubRouteCode;
            target.Status = source.Status;

            target.NumOfPax = source.NumOfPax;
            target.IsShuttle = source.IsShuttle;

            target.ShuttlePrice = source.ShuttlePrice;

            target.ExtraService = source.ExtraService;

            target.CruisePackage = source.CruisePackage;
            target.CruiseCurrency = source.CruiseCurrency;
            target.CruiseRate = source.CruiseRate;

            target.SubTotal = source.SubTotal;
            target.Deposit = source.Deposit;
            target.Paid = source.Paid;
            target.PaidFee = source.PaidFee;
            target.PaidStatus = source.PaidStatus;

            target.ModifiedByID = source.ModifiedByID;
            target.ModifiedDate = source.ModifiedDate;
        }

        #endregion

        #region Delete Reservation (Use transaction)

        public bool DeleteReservation(Guid reservationID)
        {
            try
            {
                using (var db = new OBSDataContext(connectionString))
                {
                    using (var scope = new TransactionScope())
                    {
                        Reservation reservationNeedDelete =
                            db.Reservations.SingleOrDefault(x => x.ReservationID == reservationID);

                        if (reservationNeedDelete != null)
                        {
                            // Delete all block cabin
                            IQueryable<CabinStatusEntity> cabinstatus =
                                db.CabinStatusEntities.Where(x => x.ReservationDetail.ReservationID == reservationID);
                            db.CabinStatusEntities.DeleteAllOnSubmit(cabinstatus);

                            // Delete all details
                            IQueryable<ReservationDetail> detailsNeedDelete =
                                db.ReservationDetails.Where(x => x.ReservationID == reservationID);
                            db.ReservationDetails.DeleteAllOnSubmit(detailsNeedDelete);

                            // Delete all service (program)
                            IQueryable<ReservationService> servicesNeedDelete =
                                db.ReservationServices.Where(x => x.ReservationID == reservationID);
                            db.ReservationServices.DeleteAllOnSubmit(servicesNeedDelete);

                            // Delete all extra
                            IQueryable<ReservationExtra> extrasNeedDelete =
                                db.ReservationExtras.Where(x => x.ReservationID == reservationID);
                            db.ReservationExtras.DeleteAllOnSubmit(extrasNeedDelete);

                            // Delete all payment details batch
                            IQueryable<PaymentBatchDetail> batchdetails =
                                db.PaymentBatchDetails.Where(
                                    x =>
                                        x.PaymentBatch.ReservationPayments.Count(
                                            y => y.ReservationID == reservationID) > 0);
                            db.PaymentBatchDetails.DeleteAllOnSubmit(batchdetails);

                            // Get batches
                            IQueryable<PaymentBatch> batches =
                                db.PaymentBatches.Where(
                                    x => x.ReservationPayments.Count(y => y.ReservationID == reservationID) > 0);

                            // Delete all payment
                            IQueryable<ReservationPayment> paymentNeedDelete =
                                db.ReservationPayments.Where(x => x.ReservationID == reservationID);
                            db.ReservationPayments.DeleteAllOnSubmit(paymentNeedDelete);

                            // Delete batches
                            db.PaymentBatches.DeleteAllOnSubmit(batches);

                            db.Reservations.DeleteOnSubmit(reservationNeedDelete);
                            db.SubmitChanges();

                            scope.Complete();
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                return false;
            }
        }

        #endregion

        #endregion

        /// <summary>
        /// Get payment history
        /// </summary>
        /// <param name="reservationID"></param>
        /// <returns></returns>
        public IList<PaymentHistoryDTO> GetPaymentHistory(Guid? reservationID)
        {
            IList<PaymentHistoryDTO> histories;
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<PaymentHistoryDTO> query = from x in db.ReservationPayments
                    join y in db.PaymentBatchDetails on x.PaymentBatch.PaymentBatchID equals y.PaymentBatchID
                    where (reservationID == null || x.ReservationID == reservationID)
                    select new PaymentHistoryDTO
                    {
                        PaymentBatchDetailID = y.PaymentBatchDetailID,
                        Date = x.PaymentBatch.PaymentDate,
                        PaymentMethod = ((PaidMethodEnum) y.PaymentMethod).GetStringValue(),
                        Amount = y.Amount,
                        Currency =
                            y.CurrencyAmount.HasValue ? ((CurrencyEnum) y.CurrencyAmount.Value).GetStringValue() : "VND",
                        VNDAmount = y.Amount*y.Rate
                    };
                histories = query.ToList();
            }
            return histories;
        }

        /// <summary>
        ///     Get Service
        /// </summary>
        /// <param name="departureDate"></param>
        /// <returns></returns>
        public object GetServiceCalendar(DateTime departureDate)
        {
            object list;
            using (var db = new OBSDataContext(connectionString))
            {
                db.DeferredLoadingEnabled = false;

                DateTime low = departureDate;
                DateTime high = departureDate.AddDays(7);
                // Lines
                IQueryable<Temp> queryService =
                    db.ReservationServices.Between(x => x.Date, low, high)
                        .Select(
                            x =>
                                new Temp
                                {
                                    ReservationID = x.ReservationID,
                                    BookingCode = x.Reservation.ReservationCode,
                                    Boat = x.Reservation.BoatID,
                                    Service = x
                                });

                // Date list
                var dates = new List<DateTime>();
                for (DateTime i = low; i < high; i = i.AddDays(1))
                {
                    dates.Add(i);
                }

                // Convert to the cross tab report
                var query = queryService.GroupBy(c => new {c.ReservationID, c.BookingCode, c.Boat}).Select(g =>
                    new
                    {
                        g.Key.ReservationID,
                        g.Key.BookingCode,
                        g.Key.Boat,
                        Service1 = ConvertToService(g.Where(c => c.Service.Date == dates[0] && c.Service.Active)),
                        Service2 = ConvertToService(g.Where(c => c.Service.Date == dates[1] && c.Service.Active)),
                        Service3 = ConvertToService(g.Where(c => c.Service.Date == dates[2] && c.Service.Active)),
                        Service4 = ConvertToService(g.Where(c => c.Service.Date == dates[3] && c.Service.Active)),
                        Service5 = ConvertToService(g.Where(c => c.Service.Date == dates[4] && c.Service.Active)),
                        Service6 = ConvertToService(g.Where(c => c.Service.Date == dates[5] && c.Service.Active)),
                        Service7 = ConvertToService(g.Where(c => c.Service.Date == dates[6] && c.Service.Active))
                    }).OrderBy(x => x.BookingCode).ToList();

                list = query;
            }

            return new {result = list};
        }

        private IList<ReservationService> ConvertToService(object reservationService)
        {
            var list = (reservationService as IList<Temp>);
            if (list != null && list.Count > 0)
                return list.Select(x => x.Service).ToList();
            return null;
        }

        #region Extensibility Method Declarations

        public void InsertCollection(List<Reservation> ls)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                db.Reservations.InsertAllOnSubmit(ls);
                db.SubmitChanges();
            }
        }

        public List<Reservation> GetReservationAll()
        {
            using (var db = new OBSDataContext(connectionString))
            {
                return db.Reservations.ToList();
            }
        }

        public object GetQuickSearch(string keyword)
        {
            keyword = keyword.ToLower();
            using (var db = new OBSDataContext(connectionString))
            {
                var reservations =
                    db.Reservations.Where(x => x.ReservationCode.ToLower().Contains(keyword))
                        .Take(18)
                        .Select(
                            x =>
                                new
                                {
                                    label = x.ReservationCode,
                                    category = "Reservation",
                                    url = "/Pages/Reservations/ReservationEdit.aspx?ReservationID=" + x.ReservationID
                                });
                var services =
                    db.Services.Where(x => x.ServiceName.ToLower().Contains(keyword))
                        .Take(18)
                        .Select(
                            x =>
                                new
                                {
                                    label = x.ServiceName,
                                    category = "Service",
                                    url = "/Pages/Operators/ServiceItem.aspx?ServiceID=" + x.ServiceID
                                });

                return reservations.Union(services).OrderBy(x => x.category).ToList();
            }
        }

        public Reservation GetReservationById(Guid reservationID)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                return db.Reservations.SingleOrDefault(a => a.ReservationID == reservationID);
            }
        }

        public List<Reservation> SelectListItem(Guid? reservationID, string reservationCode, string guestID,
            DateTime? checkInDate, string boatID, int? routeNumber, char? subRouteCode, int? numOfCabins, int? totalDays,
            DateTime? alarm, int? statusReservation, string note, bool? pickup, string hotelAddress, bool? isShuttle,
            decimal? shuttlePrice, decimal? totalShuttlePrice, bool? isService, decimal? serviceFee,
            decimal? totalAmount, decimal? discount, decimal? charge, decimal? deposit, decimal? balance,
            bool? paidStatus, string createdByID, DateTime? creationDate, string modifiedByID, DateTime? modifiedDate,
            HashSet<string> sortClause)
        {
            int count;
            return SelectListItem(reservationID, reservationCode, guestID, checkInDate, boatID, routeNumber,
                subRouteCode, numOfCabins, totalDays, alarm, statusReservation, note, pickup, hotelAddress, isShuttle,
                shuttlePrice, totalShuttlePrice, isService, serviceFee, totalAmount, discount, charge, deposit, balance,
                paidStatus, createdByID, creationDate, modifiedByID, modifiedDate, null, null, out count, sortClause);
        }

        public List<Reservation> SelectListItem(Guid? reservationID, string reservationCode, string guestID,
            DateTime? checkInDate, string boatID, int? routeNumber, char? subRouteCode, int? numOfCabins,
            int? numOfNights, DateTime? alarm, int? statusReservation, string note, bool? pickup, string hotelAddress,
            bool? isShuttle, decimal? shuttlePrice, decimal? totalShuttlePrice, bool? isService, decimal? serviceFee,
            decimal? totalAmount, decimal? discount, decimal? charge, decimal? deposit, decimal? balance,
            bool? paidStatus, string createdByID, DateTime? creationDate, string modifiedByID, DateTime? modifiedDate,
            int? page, int? row, out int count, HashSet<string> sortClause)
        {
            using (var db = new OBSDataContext(connectionString))
            {
                IQueryable<Reservation> query = (from table in db.Reservations select table);
                if (reservationID.HasValue)
                    query = query.Where(table => table.ReservationID == reservationID.Value);
                if (!reservationCode.Equals(string.Empty))
                    query = query.Where(table => table.ReservationCode.ToLower().Equals(reservationCode.ToLower()));
                if (!guestID.Equals(string.Empty))
                    query = query.Where(table => table.GuestID.ToLower().Equals(guestID.ToLower()));
                if (checkInDate.HasValue)
                    query = query.Where(table => table.CheckInDate == checkInDate.Value.Date);
                if (!boatID.Equals(string.Empty))
                    query = query.Where(table => table.BoatID.ToLower().Equals(boatID.ToLower()));
                if (routeNumber.HasValue)
                    query = query.Where(table => table.RouteNumber == routeNumber.Value);
                if (subRouteCode.HasValue)
                    query = query.Where(table => table.SubRouteCode == subRouteCode.Value);
                if (numOfCabins.HasValue)
                    query = query.Where(table => table.NumOfCabins == numOfCabins.Value);
                if (numOfNights.HasValue)
                    query = query.Where(table => table.NumOfNights == numOfNights.Value);
                if (alarm.HasValue)
                    query = query.Where(table => table.Alarm == alarm.Value.Date);
                if (statusReservation.HasValue)
                    query = query.Where(table => table.Status == statusReservation.Value);
                if (!note.Equals(string.Empty))
                    query = query.Where(table => table.Note.ToLower().Equals(note.ToLower()));
                if (pickup.HasValue)
                    query = query.Where(table => table.Pickup == pickup.Value);
                if (!hotelAddress.Equals(string.Empty))
                    query = query.Where(table => table.HotelAddress.ToLower().Equals(hotelAddress.ToLower()));
                if (isShuttle.HasValue)
                    query = query.Where(table => table.IsShuttle == isShuttle.Value);
                if (shuttlePrice.HasValue)
                    query = query.Where(table => table.ShuttlePrice == shuttlePrice.Value);
                if (serviceFee.HasValue)
                    query = query.Where(table => table.ExtraService == serviceFee.Value);
                if (totalAmount.HasValue)
                    query = query.Where(table => table.SubTotal == totalAmount.Value);
                if (charge.HasValue)
                    query = query.Where(table => table.Paid == charge.Value);
                if (deposit.HasValue)
                    query = query.Where(table => table.Deposit == deposit.Value);
                if (paidStatus.HasValue)
                    query = query.Where(table => table.PaidStatus == paidStatus.Value);
                if (!createdByID.Equals(string.Empty))
                    query = query.Where(table => table.CreatedByID.ToLower().Equals(createdByID.ToLower()));
                if (creationDate.HasValue)
                    query = query.Where(table => table.CreationDate == creationDate.Value.Date);
                if (!modifiedByID.Equals(string.Empty))
                    query = query.Where(table => table.ModifiedByID.ToLower().Equals(modifiedByID.ToLower()));
                if (modifiedDate.HasValue)
                    query = query.Where(table => table.ModifiedDate == modifiedDate.Value.Date);
                for (int i = sortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(sortClause.ElementAt(i));
                count = query.Count();
                if (page.HasValue && row.HasValue)
                    return query.PageIQuery(page.Value, row.Value).ToList();
                return query.ToList();
            }
        }

        #endregion

        private class Temp
        {
            public string Boat = String.Empty;
            public string BookingCode = String.Empty;
            public Guid ReservationID = Guid.Empty;
            public ReservationService Service;
        }
    }
}